#!/bin/bash

if [ $# -ne 2 ];
then
    echo "usage sh create_database.sh 数据库名称 授权账号"
    exit
fi

db=$1
user_name=$2
#backup_dir="/nas-ops/prod_rds_backup/"
set_connect_mysql_env(){
prod_db_host="自己的生产环境的mysql.rds.aliyuncs.com"
sit_db_host="自己的测试环境的mysql.rds.aliyuncs.com"
uat_db_host="自己的预发布环境的mysql.rds.aliyuncs.com"
dev_db_host="自己的开发环境的mysql.rds.aliyuncs.com"
db_user="自己的admin账户"
db_passwd="自己的admin账户的密码"
MYSQL=/usr/bin/mysql
MYSQLDUMP=/usr/bin/mysqldump
date=$(date +"%Y-%m-%d")
}


###生成15位密码给不同环境下新创建的数据库
create_env_password_dev(){
	dev=`openssl rand 100 | base64 | head -c 15`
	echo "dev's passwd is $dev"
}
create_env_password_sit(){
	sit=`openssl rand 100 | base64 | head -c 15`
	echo "sit's passwd is $sit"
}
create_env_password_uat(){
	uat=`openssl rand 100 | base64 | head -c 15`
	echo "uat's passwd is $uat"
}
create_env_password_prod(){
	prod=`openssl rand 100 | base64 | head -c 15`
	echo "prod's passwd is $prod"
}


###创建不同环境数据库##
#create_db_sql="create database IF NOT EXISTS ${DBNAME}"
#mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "${create_db_sql}"



create_database_dev(){
echo "创建dev数据库开始..."
#dev
$MYSQL -h $dev_db_host -u $db_user -p$db_passwd <<EOF  
create database $db default character set utf8mb4 collate utf8mb4_unicode_ci;
EOF
echo "创建dev数据库结束..."
}
create_database_sit(){
echo "创建sit数据库开始..."
#sit
$MYSQL -h $sit_db_host -u $db_user -p$db_passwd <<EOF  
create database $db default character set utf8mb4 collate utf8mb4_unicode_ci;
EOF
echo "创建sit数据库结束..."
}
create_database_uat(){
echo "创建uat数据库开始..."
#uat
$MYSQL -h $uat_db_host -u $db_user -p$db_passwd <<EOF  
create database $db default character set utf8mb4 collate utf8mb4_unicode_ci;
EOF
echo "创建uat数据库结束..."
}
create_database_prod(){
echo "创建prod数据库开始..."
#prod
$MYSQL -h $prod_db_host -u $db_user -p$db_passwd <<EOF  
create database $db default character set utf8mb4 collate utf8mb4_unicode_ci;
EOF
echo "创建prod数据库结束..."
}


init_database_schema_dev(){
echo "dev 初始化数据库结构schema开始..."
$MYSQL -h $dev_db_host -u $db_user -p$db_passwd <<EOF
use $db;
source ./init_data/schema.sql;
EOF
echo "dev 初始化数据库结构schema结束..."
}
init_database_schema_sit(){
echo "sit 初始化数据库结构schema开始..."
$MYSQL -h $sit_db_host -u $db_user -p$db_passwd <<EOF
use $db;
source ./init_data/schema.sql;
EOF
echo "sit 初始化数据库结构schema结束..."
}
init_database_schema_uat(){
echo "uat 初始化数据库结构schema开始..."
$MYSQL -h $uat_db_host -u $db_user -p$db_passwd <<EOF
use $db;
source ./init_data/schema.sql;
EOF
echo "uat 初始化数据库结构schema结束..."
}
init_database_schema_prod(){
echo "prod 初始化数据库结构schema开始..."
$MYSQL -h $prod_db_host -u $db_user -p$db_passwd <<EOF
use $db;
source ./init_data/schema.sql;
EOF
echo "prod 初始化数据库结构schema结束..."
}


init_database_dev(){
echo "dev 创建账号，授权开始..."
#dev
$MYSQL -h $dev_db_host -u $db_user -p$db_passwd <<EOF
CREATE USER '$user_name'@'%' IDENTIFIED BY '$dev';
GRANT ALL PRIVILEGES ON $db.* TO '$user_name'@'%';
flush  privileges ;

EOF
echo "dev 创建账号，授权结束..."
}



init_database_sit(){
echo "sit 创建账号，授权开始..."
#sit
$MYSQL -h $sit_db_host -u $db_user -p$db_passwd <<EOF
CREATE USER '$user_name'@'172.18.%.%' IDENTIFIED BY '$sit';    
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, EXECUTE, SHOW VIEW, EVENT, TRIGGER ON $db.* TO '$user_name'@'172.18.%.%';

CREATE USER '$user_name'@'192.168.72.%' IDENTIFIED BY '$sit';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, EXECUTE, SHOW VIEW, EVENT, TRIGGER ON $db.* TO '$user_name'@'192.168.72.%';

CREATE USER '$user_name'@'47.106.170.67' IDENTIFIED BY '$sit';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, EXECUTE, SHOW VIEW, EVENT, TRIGGER ON $db.* TO '$user_name'@'47.106.170.67';
 
 
CREATE USER '$user_name'@'192.168.29.%' IDENTIFIED BY '$sit';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, EXECUTE, SHOW VIEW, EVENT, TRIGGER ON $db.* TO '$user_name'@'192.168.29.%';

CREATE USER '$user_name'@'120.78.192.137' IDENTIFIED BY '$sit';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, EXECUTE, SHOW VIEW, EVENT, TRIGGER ON $db.* TO '$user_name'@'120.78.192.137';
 
 
CREATE USER '$user_name'@'10.4%' IDENTIFIED BY '$sit';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, EXECUTE, SHOW VIEW, EVENT, TRIGGER ON $db.* TO '$user_name'@'10.4%';
flush  privileges ;
 
 
CREATE USER '$user_name'@'%' IDENTIFIED BY '$sit';
GRANT SELECT, LOCK TABLES, EXECUTE, SHOW VIEW ON $db.* TO '$user_name'@'%';
flush  privileges ;
use $db;
source ./init_data/schema.sql;
EOF

echo "sit 创建账号，授权结束..."
}


init_database_uat(){
echo "uat 创建账号，授权开始..."
#uat
$MYSQL -h $uat_db_host -u $db_user -p$db_passwd <<EOF
CREATE USER '$user_name'@'120.79.132.181' IDENTIFIED BY '$uat';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, EXECUTE, SHOW VIEW, EVENT, TRIGGER ON $db.* TO '$user_name'@'120.79.132.181';
 
CREATE USER '$user_name'@'192.168.30.%' IDENTIFIED BY '$uat';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, EXECUTE, SHOW VIEW, EVENT, TRIGGER ON $db.* TO '$user_name'@'192.168.30.%';
 
CREATE USER '$user_name'@'121.201.72%' IDENTIFIED BY '$uat';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, EXECUTE, SHOW VIEW, EVENT, TRIGGER ON $db.* TO '$user_name'@'121.201.72%';
 
CREATE USER '$user_name'@'192.168.72.%' IDENTIFIED BY '$uat';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, EXECUTE, SHOW VIEW, EVENT, TRIGGER ON $db.* TO '$user_name'@'192.168.72.%';
 
CREATE USER '$user_name'@'172.2%' IDENTIFIED BY '$uat';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, EXECUTE, SHOW VIEW, EVENT, TRIGGER ON $db.* TO '$user_name'@'172.2%';
 
CREATE USER '$user_name'@'%' IDENTIFIED BY '$uat';
GRANT SELECT, EXECUTE, SHOW VIEW ON $db.* TO '$user_name'@'%';
 
flush  privileges;
use $db;
source ./init_data/schema.sql;
EOF

echo "dev 创建账号，授权结束..."
}


init_database_prod(){
echo "prod 创建账号，授权开始..."
#prod
$MYSQL -h $prod_db_host -u $db_user -p$db_passwd <<EOF
CREATE USER '$user_name'@'120.24.73.177' IDENTIFIED BY '$prod';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, EXECUTE, SHOW VIEW, EVENT, TRIGGER ON $db.* TO '$user_name'@'120.24.73.177';
 
CREATE USER '$user_name'@'192.168.31.%' IDENTIFIED BY '$prod';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, EXECUTE, SHOW VIEW, EVENT, TRIGGER ON $db.* TO '$user_name'@'192.168.31.%';

CREATE USER '$user_name'@'192.168.4%' IDENTIFIED BY '$prod';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, EXECUTE, SHOW VIEW, EVENT, TRIGGER ON $db.* TO '$user_name'@'192.168.4%';
 
CREATE USER '$user_name'@'121.201.72%' IDENTIFIED BY '$prod';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, EXECUTE, SHOW VIEW, EVENT, TRIGGER ON $db.* TO '$user_name'@'121.201.72%';
 
CREATE USER '$user_name'@'172.2%' IDENTIFIED BY '$prod';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, EXECUTE, SHOW VIEW, EVENT, TRIGGER ON $db.* TO '$user_name'@'172.2%';
 
CREATE USER '$user_name'@'192.168.72.%' IDENTIFIED BY '$prod';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, EXECUTE, SHOW VIEW, EVENT, TRIGGER ON $db.* TO '$user_name'@'192.168.72.%';
 
CREATE USER '$user_name'@'%' IDENTIFIED BY '$prod';
GRANT SELECT, EXECUTE, SHOW VIEW ON $db.* TO '$user_name'@'%';
 
flush  privileges;
EOF

echo "prod 创建账号，授权结束..."
}



###添加数据
add_data_dev(){
echo "dev 添加数据开始..."
#dev  
for file in `ls ./init_data/t_*.sql`
do
$MYSQL -h $dev_db_host -u $db_user -p$db_passwd <<EOF  
use $db;
source ${file};
EOF
done
echo "dev 添加数据结束..."
}
add_data_sit(){
echo "sit 添加数据开始..."
#sit
for file in `ls ./init_data/t_*.sql`
do
$MYSQL -h $sit_db_host -u $db_user -p$db_passwd <<EOF  
use $db;
source ${file};
EOF
done
echo "sit 添加数据结束..."
}
add_data_uat(){
echo "uat 添加数据开始..."
#uat
for file in `ls ./init_data/t_*.sql`
do
$MYSQL -h $uat_db_host -u $db_user -p$db_passwd <<EOF  
use $db;
source ${file};
EOF
done
echo "uat 添加数据结束..."
}
add_data_prod(){
echo "prod 添加数据开始..."
#prod
for file in `ls ./init_data/t_*.sql`
do
$MYSQL -h $prod_db_host -u $db_user -p$db_passwd <<EOF  
use $db;
source ${file};
EOF
done

echo "prod 添加数据结束..."	
}

# 固定的连接数据库的信息，必定执行
set_connect_mysql_env

# 若已经创建请指定密码,并注释掉 生成数据库的密码
#dev=
#sit=
#uat=123455
#prod=
# 若要生成不同环境的数据库的密码，需注释掉上面的指定密码
#create_env_password_dev
create_env_password_sit
create_env_password_uat
create_env_password_prod

# 创建不同环境的数据库
#create_database_dev
create_database_sit
create_database_uat
create_database_prod

# 创建授权
#init_database_dev
init_database_sit
init_database_uat
init_database_prod

# 初始化不同环境的数据库结构
# init_database_schema_dev
init_database_schema_sit
init_database_schema_uat
init_database_schema_prod


# 添加不同环境的数据
#add_data_dev
add_data_sit
add_data_uat
add_data_prod
